<?php
namespace app\controllers;
/**
 * mysql工具类
 * @author dt
 * @time 2017/03/3
 */

class Mysql
{
    //根据sql语句 来查询多条数据
    public function actionGetSelectall()
    {
        $post['condition']=HoComm::getParam($_POST['condition'])?$_POST['condition']:'';
        $post['dbname']=HoComm::getParam($_POST['dbname'])?$_POST['dbname']:'';
        if(!empty($post['condition']) && !empty($post['dbname'])){
            $dbname=$post['dbname']; $sql='select '.$post['condition'];
            $result=Yii::app()->$dbname->createCommand($sql)->queryAll();
            if(!empty($result)){
                echo $this->ajaxData($result);
            }else{
                echo $this->ajaxData($result,'The query is successful, returns the result is empty',220);
            }
        }else{
            echo $this->ajaxData('','Parameter error ',210);
        }
    }

    //拼接select 、 order by 特殊词语
    public function actionGetSelectallOrderby()
    {
        $post['condition']=HoComm::getParam($_POST['condition'])?$_POST['condition']:'';
        $post['dbname']=HoComm::getParam($_POST['dbname'])?$_POST['dbname']:'';
        if(!empty($post['condition']) && !empty($post['dbname'])){
            $order_sql=$this->selectorderby($post['num']);
            $dbname=$post['dbname']; $sql='select '.$post['condition'].$order_sql;
            $result=Yii::app()->$dbname->createCommand($sql)->queryAll();
            if(!empty($result)){
                echo $this->ajaxData($result);
            }else{
                echo $this->ajaxData($result,'The query is successful, returns the result is empty',220);
            }
        }else{
            echo $this->ajaxData('','Parameter error ',210);
        }
    }


    //根据sql语句，来查询一条数据
    public function actionGetselectone()
    {
        $post['condition']=HoComm::getParam($_POST['condition'])?$_POST['condition']:'';
        $post['dbname']=HoComm::getParam($_POST['dbname'])?$_POST['dbname']:'';
        if(!empty($post['condition'] && !empty($post['dbname']))) {
            $dbname=$post['dbname'];  $sql='select '.$post['condition'];
            $result = Yii::app()->$dbname->createCommand($sql)->queryScalar();
            if(!empty($result)){
                echo $this->ajaxData($result);
            }else{
                echo $this->ajaxData($result,'The query is successful, returns the result is empty',220);
            }
        }else{
            echo $this->ajaxData('','Parameter error',210);
        }
    }

    //根据sql语句，插入一条数据
    public function actionPutinsertone()
    {
        $post['condition']=HoComm::getParam($_POST['condition'])?$_POST['condition']:'';
        $post['dbname']=HoComm::getParam($_POST['dbname'])?$_POST['dbname']:'';
        if(!empty($post['condition'] && !empty($post['dbname']))) {
            $dbname=$post['dbname'];  $sql='insert into '.$post['condition'];
            $result = Yii::app()->$dbname->createCommand($sql)->execute();
            if (!empty($result)) {
                echo $this->ajaxData($result);  //返回数据 业务可能需要知道添加成功后新增id值
            } else {
                echo $this->ajaxData(false,'Save failed',240);
            }
        }else{
            echo $this->ajaxData('','Parameter error',210);
        }
    }

    //根据sql语句，修改一条数据
    public function actionPutupdateone()
    {
        $post['condition']=HoComm::getParam($_POST['condition'])?$_POST['condition']:'';
        $post['dbname']=HoComm::getParam($_POST['dbname'])?$_POST['dbname']:'';
        if(!empty($post['condition'] && !empty($post['dbname']))) {
            $dbname=$post['dbname'];  $sql='update '.$post['condition'];
            $result = Yii::app()->$dbname->createCommand($sql)->execute();
            if (!empty($result)) {
                echo $this->ajaxData($result);  //返回数据 业务可能需要知道添加成功后新增id值
            } else {
                echo $this->ajaxData(false,'Save failed',240);
            }
        }else{
            echo $this->ajaxData('','Parameter error',210);
        }
    }

    //删除一条数据  非主键
    public function actionMovedeleteone()
    {
        $tablename=HoComm::getParam($_POST['tablename'])?$_POST['tablename']:'';
        $params=HoComm::getParam($_POST['params'])?$_POST['params']:'';
        if(!empty($tablename) && !empty($params)) {
            $result = $tablename::model()->deleteAll($params);
            if (!empty($result)) {
                echo $this->ajaxData($result);
            } else {
                echo $this->ajaxData(false,'Delete failed',300);
            }
        }else{
            echo $this->ajaxData('','Parameter error',210);
        }
    }

    //根据主键  删除一条数据
    public function actionMovedeletepkone()
    {
        $tablename=HoComm::getParam($_POST['tablename'])?$_POST['tablename']:'';
        $id=HoComm::getParam($_POST['id'])?$_POST['id']:'';
        if(!empty($tablename) && !empty($id)) {
            $result = $tablename::model()->deleteByPk($id);
            if (!empty($result)) {
                echo $this->ajaxData(true);
            } else {
                echo $this->ajaxData(false,'Delete failed',300);
            }
        }else{
            echo $this->ajaxData('','Parameter error',210);
        }
    }

    //根据主键 修改一条数据
    protected function actionUpdateone()
    {
        $tablename=HoComm::getParam($_POST['tablename'])?$_POST['tablename']:'';
        $params=HoComm::getParam($_POST['$params'])?$_POST['params']:'';
        $array=HoComm::getParam($_POST['array'])?$_POST['array']:'';
        if(!empty($tablename) && !empty($params) && !empty($array)) {
            $result = $tablename::model()->updateByPk($params, $array);
            if (!empty($result)) {
                echo $this->ajaxData(true);
            } else {
                echo $this->ajaxData(false,'Save failed',240);
            }
        }else{
            echo $this->ajaxData('','Parameter error',210);
        }
    }

    protected function ajaxData($result='',$info='success',$code=200)
    {
        return json_encode(array(
            'code' => $code,
            'message' => $info,
            'data' => $result,
        ));
    }

    protected function selectorderby($num)
    {
        switch($num){
            case 1:
                return ' order by UNIX_TIMESTAMP(create_time) desc ,house_name';
                break;
            case 2:
                return ' order by UNIX_TIMESTAMP(create_time) desc ,house_name limit 1';
                break;
            case 3:
                return ' order by house_name';
                break;
            case 4:
                return ' order by UNIX_TIMESTAMP(h.create_time) desc ,h.house_name';
                break;
        }
    }
}

